package br.com.mundodaprata.store.data.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

import br.com.mundodaprata.store.data.NamedRepository;
import br.com.mundodaprata.store.model.Product;
import br.com.mundodaprata.store.util.Utilities;

public class ProductRepositoryJDBC extends RepositoryJDBC<Product> implements
      NamedRepository<Product> {
   
   public ProductRepositoryJDBC() {
      super();
   }
   
   public ProductRepositoryJDBC(Connection connection) {
      super(connection);
   }
   
   @Override
   public void insert(Product... elements) throws Exception {
      String sql = "insert into product(name, model, stock, min_stock, price) values(?, ?, ?, ?, ?);";
      
      PreparedStatement stmt = connection().prepareStatement(sql);
      
      try {
         for (Product product : elements) {
            stmt.setString(1, product.getName());
            
            String model = product.getModel();
            
            if (Utilities.isEmpty(model)) {
               stmt.setNull(2, Types.VARCHAR);
            }
            else {
               stmt.setString(2, product.getModel());
            }
            
            stmt.setInt(3, product.getStock());
            stmt.setInt(4, product.getMinStock());
            stmt.setDouble(5, product.getPrice());
            
            stmt.addBatch();
         }
         
         stmt.executeBatch();
      }
      finally {
         if (stmt != null && !stmt.isClosed()) {
            stmt.close();
         }
      }
   }
   
   @Override
   public void update(Product... elements) throws Exception {
      String sql = "update product set name = ?, model = ?, stock = ?, min_stock = ?, price = ? where code =?;";
      
      PreparedStatement stmt = connection().prepareStatement(sql);
      
      try {
         for (Product product : elements) {
            stmt.setString(1, product.getName());
            
            String model = product.getModel();
            
            if (Utilities.isEmpty(model)) {
               stmt.setNull(2, Types.VARCHAR);
            }
            else {
               stmt.setString(2, product.getModel());
            }
            
            stmt.setInt(3, product.getStock());
            stmt.setInt(4, product.getMinStock());
            stmt.setDouble(5, product.getPrice());
            stmt.setLong(6, product.getCode());
            
            stmt.addBatch();
         }
         
         stmt.executeBatch();
      }
      finally {
         if (stmt != null && !stmt.isClosed()) {
            stmt.close();
         }
      }
   }
   
   @Override
   public void remove(Product... elements) throws Exception {
      String sql = "delete from product where code =?;";
      
      PreparedStatement stmt = connection().prepareStatement(sql);
      
      try {
         for (Product product : elements) {
            stmt.setLong(1, product.getCode());
            
            stmt.addBatch();
         }
         
         stmt.executeBatch();
      }
      finally {
         if (stmt != null && !stmt.isClosed()) {
            stmt.close();
         }
      }
   }
   
   @Override
   public Product select(Long key) {
      String sql = "select * from product where code = ?";
      
      try {
         PreparedStatement stmt = connection().prepareStatement(sql);
         stmt.setLong(1, key);
         
         try {
            ResultSet rs = stmt.executeQuery();
            
            if (rs.next()) {
               return toProduct(rs);
            }
         }
         finally {
            if (stmt != null && !stmt.isClosed()) {
               stmt.close();
            }
         }
         
      }
      catch (SQLException e) {
         return null;
      }
      
      return null;
   }
   
   @Override
   public List<Product> all() {
      String sql = "select * from product";
      
      try {
         PreparedStatement stmt = connection().prepareStatement(sql);
         
         try {
            ResultSet rs = stmt.executeQuery();
            List<Product> result = new ArrayList<Product>();
            
            while (rs.next()) {
               result.add(toProduct(rs));
            }
            
            return result;
         }
         finally {
            if (stmt != null && !stmt.isClosed()) {
               stmt.close();
            }
         }
      }
      catch (SQLException e) {
         return Collections.emptyList();
      }
   }
   
   @Override
   public Product select(String name) {
      String sql = "select * from product where name = ?";
      
      try {
         PreparedStatement stmt = connection().prepareStatement(sql);
         stmt.setString(1, name);
         
         try {
            ResultSet rs = stmt.executeQuery();
            
            if (rs.next()) {
               return toProduct(rs);
            }
         }
         finally {
            if (stmt != null && !stmt.isClosed()) {
               stmt.close();
            }
         }
         
      }
      catch (SQLException e) {
         return null;
      }
      
      return null;
   }
   
   private Product toProduct(ResultSet rs) throws SQLException {
      Product product = new Product();
      
      product.setCode(rs.getLong("code"));
      product.setMinStock(rs.getInt("min_stock"));
      product.setModel(rs.getString("model"));
      product.setName(rs.getString("name"));
      product.setPrice(rs.getDouble("price"));
      product.setStock(rs.getInt("stock"));
      
      return product;
   }
   
}